Help IndexTable of Contents

Dynamic Text Function Reference

The functions are there to help you either manipulate the formula with static values or add further calculation options to the formulation.

NOTE: All the listed functions can be accessed from right-clicking in the Formulation Editor section. Functions that are accessible in other sections will be noted below.

Dynamic Text Functions:

Grid Context Menu Functions
Cells
Function Description Comment
Data( <row>, <column>) Gets a Cell Value Returns the raw value of the selected cell in the specified location referenced by the row and column number. Numbering is zero based and relates to the data section of the resulting grid, not the row or column labels.
FormattedData( <row>, <column>) Gets a Formatted Cell Value Returns the formatted value of the selected cell in the specified location referenced by the row and column number.
Row/Column
Function Description Comment
Label(<axis>,<row>,<column>) Get Label Value

Returns a column or row label referenced by the axis, row and column number. Axis in the formulation determines whether the lookup is completed in the row section or the column section. ( 0=columns, 1=rows)

Note that the row or column designate only the axis label section referenced, NOT the entire grid. So 'row' and 'column' provide the co-ordinates for a label only in the row descriptions section of the entire grid if the axis=1.

DataSetRow(<row>) Get Data Set Returns a set of all the values in the selected row/column

LookupMaxColumn(<row>)

LookupMaxRow(<column>)

Finds the Column/Row Number with Max Value in a given row. Returns the index of the column/row that contains the highest value for the specified row/column.

LookupMinColumn(<row>)

LookupMinRow(<column>)

Finds the Column/Row Number with Min Value in a given row. Returns the index of the column/row that contains the lowest value for the specified row/column

TotalColumn(<column>)

TotalRow(<row>)

 Sums the Column/Row Returns sum of all the values of the specified column/row

Max(DataSetColumn(<column>))

Max(DataSetRow(<row>))

 Maximum of the Column/Row Returns maximum value of the specified column/row

Min(DataSetColumn(<column>))

Min(DataSetRow(<row>))

 Minimum of the Column/Row Returns minimum value of the specified column/row

Average(DataSetColumn(<column>))

Average(DataSetRow(<row>))

 Average of the Column/Row Returns average value of the specified column/row

Median(DataSetColumn(<column>))

Median(DataSetRow(<row>))

 Median of the Column/Row Returns median value of the specified column/row

PercentileRank(n, DataSetColumn(<column>))

PercentileRank(n, DataSetRow(<row>))

 Tertile/Quartile Returns 'n' tertile (33% percentile) or quartile (25% percentile) of the values of the specified column/row
Formula Pane Context Menu Functions
General Functions:
Function Description Comment
FindColumnNumber(<caption>, <isCaseSensitive>) Find Column by Caption/Label Return the column number based on a the text caption lookup.
FindRowNumber(<caption>, <isCaseSensitive>) Find Row by Caption/Label Return the row number based on a the text caption lookup.
Data( <row>, <column>) Gets a Cell Value Returns the raw value of the selected cell in the specified location referenced by the row and column number. Numbering is zero based and relates to the data section of the resulting grid, not the row or column labels.
FormattedData( <row>, <column>) Gets a Formatted Cell Value Returns the formatted value of the selected cell in the specified location referenced by the row and column number.
Label(<axis>,<row>,<column>) Get Label

Returns a column or row label referenced by the axis, row and column number. Axis in the formulation determines whether the lookup is completed in the row section or the column section. 0=columns 1=rows.

Note that the row or column designate only the axis label section referenced, NOT the entire grid. So 'row' and 'column' provide the co-ordinates for a label only in the row descriptions section of the entire grid if the axis=1.

NumberOfColumns() Number of Columns Returns the total number of columns in the query
NumberOfRows() Number of Rows Returns the total number of rows in the query
LastColumn() Gets the Last Column Number Returns the index of the last column from the resulting grid rendered at runtime
LastRow() Gets the Last Row Number Returns the index of the last row from the resulting grid rendered at runtime
LookupMaxColumn(<row>) Finds the Column Number with Max Value in a given row. Returns the index of the column that contains the highest value for the specified row.
LookupMaxRow(<column>) Finds the Row Number with Max Value in a given column. Returns the index of the row that contains the highest value for the specified column.
LookupMinColumn(<row>) Finds the Column Number with Min Value in a given row. Returns the index of the column that contains the lowest value for the specified row
LookupMinRow(<column>) Find Row Number with Min Value in a given column. Returns the index of the column that contains the lowest value for the specified column
TotalColumn(<column>) Sums the Column Returns the sum of all values of the specified column.
TotalRow(<row>) Sums the Row Returns sum of all the values of the specified row
TotalGrid() Sums the entire Grid Returns the sum of all values of the selected grid.
MaxGrid() Finds the Max Value in the grid Returns the highest value found within the selected grid.
MinGrid() Finds the Min Value in the grid Returns the lowest value found within the selected grid.
IsEmptyQuery() Query has no results Returns true if the query has no results. Useful for checking and preventing dynamic formulae from breaking because there is no underlying data.
Advanced Functions:
Logic Name Comment
DataSetColumn(<column>) Array/List of data from a Column Returns a set of all the values in the selected column
DataSetRow(<row>) Array/List of data from a Row Returns a set of all the values in the selected row
RemoveBlanks([<value_set>]) Remove Blanks This will produce a value set where all the blank or null values ("Nan") have been removed from the list. Useful for when calculating things and you want to exclude null values or when the calculation doesn't work with null values.
Sum([<value_set>]) Sums a list of data The value set needed can be derived from the DataSetColumn or DataSetRow functions.
Max([<value_set>]) Maximum value in a list of data The value set needed can be derived from the DataSetColumn or DataSetRow functions.
Min([<value_set>]) Minimum value in a list of data The value set needed can be derived from the DataSetColumn or DataSetRow functions.
Average([<value_set>]) Average of values in a list The value set needed can be derived from the DataSetColumn or DataSetRow functions.
Median([<value_set>]) Median of values in a list The value set needed can be derived from the DataSetColumn or DataSetRow functions.
StandardDeviation([<value_set>]) Standard Deviation of values in a list The value set needed can be derived from the DataSetColumn or DataSetRow functions.
Correlation([<value_set 1>], [<value_set 2>]) Correlation of values in a list The value sets needed can be derived from the DataSetColumn or DataSetRow functions.
Covariance([<value_set 1>], [<value_set 2>]) Covariance of values in a list The value sets needed can be derived from the DataSetColumn or DataSetRow functions.
Rank(<value>, [<value_set>]) Rank of a given value as compared to other values in a list Requires the input of a value to rank against a set of values. The value set needed can be derived from the DataSetColumn or DataSetRow functions. The highest value is first.
PercentileRank(<percent>, [<value_set>]) Gives a value represented by the percentage position within the ordered list of values provided. Requires the input of a percent between 0 and 1. The value set needed can be derived from the DataSetColumn or DataSetRow functions.
Iff(<criteria>,<if_true>,<if_false>) ‘If’ conditional logic This is used to build a decision within the result of the formulation
Case(new bool[]{<criteria_set>}, new[]{<outcome_set>},<default_value>) ‘Case’ conditional logic This is used to build a decision within the result of the formulation. Here the index of the criteria should match with the index of the outcome.
IsNan(<value>) Checks for null "double" value or "Nan" This will return true if the input value is a null value. Since the values are numeric 'doubles', the null is returned as a "Nan".
Math Functions:
Logic Description Comment
Math.Abs( <value>) Absolute Value  
Math.Ceiling( <value>) Ceiling

Returns the smallest integral value that is greater than or equal to the specified number.

For example: of the value [3.2], the ceiling would be number 4
Math.Exp( <value>) Exponent Returns the number e raised to the power of the value that you have input.
Math.Floor( <value>) Floor

Returns the largest integer that is less than or equal to the specified value.

For example: of the value [3.2], the floor would be the number 3
Math.Log( <value>) Natural Log Returns the Log base 'e' value of a number
Math.Log10( <value>) Log Base 10 Returns the Log base 10 value of a number
Math.Max( <value 1>, <value 2>) Maximum Returns the larger of the two specified values.
Math.Min( <value 1>, <value 2>) Minimum Returns the smaller of the two specified values.
Math.Pow( <value x>, <value y>) Power Raises the first value to the power of the second value.
Math.Round( <value>) Round Rounds a decimal value to the nearest integral value.
constant Math.E Natural E Represents the natural logarithmic base, specified by the constant, e.
Math.PI Pi  
Date Functions:
Logic Name Description
DateTime.Now Current Date and Time Returns the current date and time at runtime.
DateTime.Parse( <text>) Parses string into Date Converts a textual input of a date into a DateTime format.
DateTime_AddYears( <date>, <value>) Adds years to date adds a specified number of years to a DateTime value.
DateTime_AddMonths( <date>, <value>) Adds months to date adds a specified number of months to a DateTime value.
DateTime_AddDays( <date>, <value>) Adds days to date adds a specified number of days to a DateTime value.
DateTime_AddHours( <date>, <value>) Adds hours to time adds a specified number of hours to a DateTime value.
DateTime_AddMinutes( <date>, <value>) Adds minutes to time adds a specified number of minutes to a DateTime value.

Query has no results – this function will return true if the query comes back with no valid data points.

Formats Functions:

Formats are used to display a specified value in a particular format. There are four categories:

Logic Name Description
Format("#,#", <value> ) Mask Formats reformats the formulation into a formatting string. For example, Format ("##,#",2147483647) -> 2,147,483,647
Format("N", <value>) Numeric Formats reformats the formulation in a specific numeric with decimals
Format("C", <value>) Currency Formats reformats the formulation in currency
Format("P", <value>) Percent Formats reformats the formulation in percentages

 

Home | Table of Contents | Index | User Community
Pyramid Analytics © 2011-2022